package cn.explink.dop.dao;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import cn.explink.dop.entity.vo.AreaVO;
import cn.explink.dop.enums.BaseInfoEnum;
import cn.explink.dop.gis.GeoCoder;
import cn.explink.dop.gis.GeoPoint;
import cn.explink.dop.gis.GeoUtility;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

@Repository("areaDao")
public class AreaDao extends GenericBaseCommonDao<AreaVO, String> {

	public AreaVO queryById(String areaId) {
		Query query = this.getSession().createQuery(" from AreaVO where id= :areaId");
		query.setString("areaId", areaId);
		return (AreaVO) query.uniqueResult();
	}

	/**
	 * 获取address所属的区域
	 *
	 * @param address
	 * @return
	 */
	public List<AreaVO> getByAddress(String address) {
		List<AreaVO> resultAreaList = new ArrayList<AreaVO>();
		GeoPoint position = GeoCoder.getInstance().getGeoCoder().GetLocationDetails(address);
		// 地理编码+POI检索失败
		if (position == null) {
			return resultAreaList;
		}
		// 获取所有区域
		// TODO 将来可能有限制条件
		List<AreaVO> areaVOList = this.finds(new AreaVO());
		// 区域返回为空
		if ((areaVOList == null) || (areaVOList.size() == 0)) {
			return resultAreaList;
		}

		// 遍历全部区域，进行空间匹配
		for (AreaVO areaVO : areaVOList) {
			String coorString = areaVO.getBaseCoordinate();
			// TODO 格式可能变化
			coorString = coorString.substring(coorString.indexOf("path") + 6, coorString.indexOf("}]") + 2);
			if (coorString == null) {
				// 区域配送范围获取失败
				continue;
			}
			@SuppressWarnings("unchecked")
			List<GeoPoint> pts = (List<GeoPoint>) JSONArray.toCollection(JSONArray.fromObject(coorString), GeoPoint.class);
			if (pts == null) // 站点的配送区域坐标查询失败
			{
				continue;
			}

			// 空间检索
			GeoPoint[] polygon = new GeoPoint[pts.size()];
			if (GeoUtility.isInPolygon(position, pts.toArray(polygon))) {
				resultAreaList.add(areaVO);
			}
		}
		return resultAreaList;
	}

	/**
	 * 获取坐标位置所属的区域
	 *
	 * @param position
	 * @return
	 */
	public List<AreaVO> getByCoordinate(String position) {
		List<AreaVO> resultAreaList = new ArrayList<AreaVO>();
		List<AreaVO> areaVOList = this.finds(new AreaVO());
		if (StringUtils.isBlank(position)) {
			return resultAreaList;
		}
		JSONObject jsonpos = JSONObject.fromObject(position);
		if (jsonpos == null) {
			return resultAreaList;
		}
		double lng = jsonpos.getDouble("lng");
		double lat = jsonpos.getDouble("lat");

		for (AreaVO areaVO : areaVOList) {
			String coorString = areaVO.getBaseCoordinate();
			// TODO 格式可能变化
			coorString = coorString.substring(coorString.indexOf("path") + 6, coorString.indexOf("}]") + 2);
			if (coorString == null) {
				// 区域配送范围获取失败
				continue;
			}
			@SuppressWarnings("unchecked")
			List<GeoPoint> pts = (List<GeoPoint>) JSONArray.toCollection(JSONArray.fromObject(coorString), GeoPoint.class);
			if (pts == null) // 站点的配送区域坐标查询失败
			{
				continue;
			}
			// 空间检索
			GeoPoint[] polygon = new GeoPoint[pts.size()];
			if (GeoUtility.isInPolygon(new GeoPoint(lng, lat), pts.toArray(polygon))) {
				resultAreaList.add(areaVO);
			}
		}

		return resultAreaList;

	}
	
	/**
	 * 
	* @Title: getByCoordinate 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @author 小强
	* @param @param position
	* @param @return    设定文件 
	* @return List<AreaVO>    返回类型 
	* @throws
	 */
	public List<AreaVO> getByCoordinateXiugai(String position,List<AreaVO> areaVOList) {
		List<AreaVO> resultAreaList = new ArrayList<AreaVO>();
		if (StringUtils.isBlank(position)) {
			return resultAreaList;
		}
		JSONObject jsonpos = JSONObject.fromObject(position);
		if (jsonpos == null) {
			return resultAreaList;
		}
		double lng = jsonpos.getDouble("lng");
		double lat = jsonpos.getDouble("lat");

		for (AreaVO areaVO : areaVOList) {
			String coorString = areaVO.getBaseCoordinate();
			// TODO 格式可能变化
			coorString = coorString.substring(coorString.indexOf("path") + 6, coorString.indexOf("}]") + 2);
			if (coorString == null) {
				// 区域配送范围获取失败
				continue;
			}
			@SuppressWarnings("unchecked")
			List<GeoPoint> pts = (List<GeoPoint>) JSONArray.toCollection(JSONArray.fromObject(coorString), GeoPoint.class);
			if (pts == null) // 站点的配送区域坐标查询失败
			{
				continue;
			}
			// 空间检索
			GeoPoint[] polygon = new GeoPoint[pts.size()];
			if (GeoUtility.isInPolygon(new GeoPoint(lng, lat), pts.toArray(polygon))) {
				resultAreaList.add(areaVO);
			}
		}

		return resultAreaList;

	}

	/**
	 * 修改基本信息
	 *
	 * @param areaVO
	 *            基本信息对象
	 */
	public void modify(final AreaVO areaVO) {
		StringBuffer sql = new StringBuffer("update bd_area set ");
		if (StringUtils.isNotBlank(areaVO.getBaseAreaName())) {
			sql.append("base_area_name='").append(areaVO.getBaseAreaName()).append("',");
		}
		if (StringUtils.isNotBlank(areaVO.getBaseCoordinate())) {
			sql.append("base_coordinate='").append(areaVO.getBaseCoordinate()).append("',");
		}
		if (0 != areaVO.getBaseEnable()) {
			sql.append("base_enable=").append(areaVO.getBaseEnable()).append(",");
		}
		if (0 != areaVO.getBaseDelete()) {
			sql.append("base_delete=").append(areaVO.getBaseDelete()).append(",");
		}
		if (StringUtils.isNotBlank(areaVO.getUpdateDate())) {
			sql.append("update_date='").append(areaVO.getUpdateDate()).append("',");
		}
		if (StringUtils.isNotBlank(areaVO.getUpdateUser())) {
			sql.append("update_user='").append(areaVO.getUpdateUser()).append("'");
		}
		if (areaVO.getMapcenterLng().compareTo(BigDecimal.ZERO) > 0) {
			sql.append("mapcenter_lng=").append(areaVO.getMapcenterLng());
		}
		if (areaVO.getMapcenterLat().compareTo(BigDecimal.ZERO) > 0) {
			sql.append("mapcenter_lat=").append(areaVO.getMapcenterLat());
		}
		sql.append(" ").append("where id=").append(areaVO.getId());
		super.getJdbcTemplate().update(sql.toString());
	}

	public void modifyByUid(final AreaVO areaVO) {
		StringBuffer sql = new StringBuffer("update bd_area set ");
		sql.append("base_coordinate=?,");
		sql.append("base_area_name=?,");
		sql.append("mapcenter_lng=?,");
		sql.append("mapcenter_lat=?,");
		sql.append("update_user=?,");
		sql.append("update_date=?");
		sql.append(" where uid='").append(areaVO.getUid()).append("'");
		super.getJdbcTemplate().update(sql.toString(), areaVO.getBaseCoordinate(), areaVO.getBaseAreaName(), areaVO.getMapcenterLng(), areaVO.getMapcenterLat(), areaVO.getUpdateUser(),
				areaVO.getUpdateDate());
	}

	/**
	 * 按照实体查询，无分页
	 *
	 * @param areaVO
	 *            查询实体
	 * @return 查询列表
	 */
	public List<AreaVO> finds(final AreaVO areaVO) {
		StringBuffer allFromSql = new StringBuffer("from AreaVO ");

		StringBuffer whereSql = this.getWhereSql(areaVO);
		if ((areaVO.getMapcenterLng() != null) && (areaVO.getMapcenterLng().compareTo(BigDecimal.ZERO) > 0)) {
			whereSql.append(" and mapcenter_lng=").append(areaVO.getMapcenterLng());
		}
		if ((areaVO.getMapcenterLng() != null) && (areaVO.getMapcenterLat().compareTo(BigDecimal.ZERO) > 0)) {
			whereSql.append(" and mapcenter_lat=").append(areaVO.getMapcenterLat());
		}

		String sql = (allFromSql.append(whereSql).toString());

		@SuppressWarnings("unchecked")
		List<AreaVO> areaList = this.getSession().createQuery(sql).list();

		return areaList;
	}

	/**
	 * 按照实体获取分页数据
	 *
	 * @param areaVO
	 *            查询实体
	 * @param pageNum
	 *            当前页
	 * @param pageSize
	 *            当前页记录条数
	 * @return 查询结果集
	 */
	public Map<String, Object> findByPage(final AreaVO areaVO, final int pageNum, final int pageSize) {
		StringBuffer allFromSql = new StringBuffer("SELECT area.id,area.base_area_name as 'baseAreaName' ");
		allFromSql.append(",area.base_coordinate as 'baseCoordinate',area.base_enable as 'baseEnable' ");
		allFromSql.append(",area.base_delete as 'baseDelete',area.create_date as 'createDate' ");
		allFromSql.append(",area.update_date as 'updateDate',area.create_user as 'createUser' ");
		allFromSql.append(",area.update_user as 'updateUser',area.mapcenter_lng as 'mapcenterLng' ");
		allFromSql.append(",area.mapcenter_lat as 'mapcenterLat',area.uid as 'uid' ");
		allFromSql.append(",us.username as 'createUserName',us2.username as 'updateUserName' ");
		allFromSql.append("FROM t_s_base_user us ,bd_area area INNER JOIN  t_s_base_user us2 ");
		allFromSql.append(" on us2.id=area.update_user where area.create_user=us.id ");

		StringBuffer countSql = new StringBuffer("select count(*) FROM t_s_base_user us ,bd_area area INNER JOIN  t_s_base_user us2 ");
		countSql.append(" on us2.id=area.update_user where area.create_user=us.id ");
		StringBuffer whereSql = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		List<Integer> paramType = new ArrayList<Integer>();
		whereSql.append(" and base_delete=1");

		if (StringUtils.isNotBlank(areaVO.getBaseAreaName())) {
			whereSql.append(" and base_area_name like ? ");
			param.add("%" + areaVO.getBaseAreaName() + "%");
			paramType.add(Types.VARCHAR);
		}
		int[] type = new int[paramType.size()];
		for (int i = 0; i < paramType.size(); i++) {
			type[i] = paramType.get(i);
		}

		Map<String, Object> map = new HashMap<String, Object>();
		countSql.append(whereSql);
		int start = (pageNum - 1) * pageSize;
		String sql = (allFromSql.append(whereSql).append(" limit " + start + "," + pageSize).toString());
		List<Map<String, Object>> list = null;
		int count = 0;
		if (type.length > 0) {
			list = this.getJdbcTemplate().queryForList(sql, param.toArray(), type);
			count = this.getJdbcTemplate().queryForInt(countSql.toString(), param, type);
		} else {
			list = this.getJdbcTemplate().queryForList(sql);
			count = this.getJdbcTemplate().queryForInt(countSql.toString());
		}

		map.put("count", count);

		map.put("list", list);
		return map;
	}

	public Map<String, Object> findVoByPage(final AreaVO areaVO, final int pageNum, final int pageSize) {
		StringBuffer allFromSql = new StringBuffer("from AreaVO ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from AreaVO ");
		StringBuffer whereSql = new StringBuffer(" where 1=1 ");
		// whereSql.append(" where base_enable=");
		// whereSql.append(BaseInfoEnum.Enable.getValue());
		// whereSql.append(" and base_delete=").append(BaseInfoEnum.Used.getValue());
		List<Object> param = new ArrayList<Object>();
		whereSql.append(" and base_delete=1");

		if (StringUtils.isNotBlank(areaVO.getBaseAreaName())) {
			whereSql.append(" and base_area_name like ? ");
			param.add("%" + areaVO.getBaseAreaName() + "%");
		}
		if (areaVO.getBaseEnable() != 0) {
			whereSql.append(" and base_enable=? ");
			param.add(areaVO.getBaseEnable());
		}
		Map<String, Object> map = new HashMap<String, Object>();

		String sql = (allFromSql.append(whereSql).toString());
		Query queryCount = this.getSession().createQuery((countFromSql.append(whereSql).toString()));
		Query queryList = this.getSession().createQuery(sql);
		for (int i = 0; i < param.size(); i++) {
			queryCount.setParameter(i, param.get(i));
			queryList.setParameter(i, param.get(i));
		}
		Integer count = ((Long) queryCount.uniqueResult()).intValue();

		map.put("count", count);

		@SuppressWarnings("unchecked")
		List<AreaVO> areaList = queryList.setFirstResult((pageNum - 1) * pageSize).setMaxResults(pageSize).list();

		map.put("list", areaList);
		return map;
	}

	private StringBuffer getWhereSql(final AreaVO areaVO) {
		StringBuffer whereSql = new StringBuffer(" where 1=1 ");
		// whereSql.append(" where base_enable=");
		// whereSql.append(BaseInfoEnum.Enable.getValue());
		whereSql.append(" and base_delete=").append(BaseInfoEnum.Used.getValue());

		if (StringUtils.isNotBlank(areaVO.getBaseAreaName())) {
			whereSql.append(" and base_area_name='").append(areaVO.getBaseAreaName()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getId())) {
			whereSql.append(" and id='").append(areaVO.getId()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getBaseCoordinate())) {
			whereSql.append(" and base_coordinate='").append(areaVO.getBaseCoordinate()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getUpdateDate())) {
			whereSql.append(" and creat_date='").append(areaVO.getCreateDate()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getCreateUser())) {
			whereSql.append(" and creat_user='").append(areaVO.getCreateUser()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getUpdateDate())) {
			whereSql.append(" and update_date='").append(areaVO.getUpdateDate()).append("'");
		}
		if (StringUtils.isNotBlank(areaVO.getCreateUser())) {
			whereSql.append(" and update_user='").append(areaVO.getUpdateUser()).append("'");
		}
		if ((areaVO.getBaseEnable() == 1) || (areaVO.getBaseEnable() == 2)) {
			whereSql.append(" and base_enable=" + areaVO.getBaseEnable());
		}
		return whereSql;
	}

	/**
	 * 删除选中对象
	 *
	 * @param baseInfoList
	 *            参数集合
	 */
	public int delete(final List<AreaVO> baseInfoList) {
		StringBuffer sql = new StringBuffer("update bd_area set ");
		sql.append("base_delete=").append(BaseInfoEnum.Delete.getValue());
		TreeSet<String> set = new TreeSet<String>();
		for (AreaVO b : baseInfoList) {
			set.add("'" + b.getId() + "'");
		}
		sql.append(" ").append("where id in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		return super.getJdbcTemplate().update(sql.toString());
	}

	/**
	 *
	 * @Title: unEnabled
	 * @Description: 将启用状态改为禁用
	 * @author 孟凡岭
	 * @param @param areaIdList
	 * @param @return
	 * @return boolean 返回类型
	 * @throws
	 */
	public boolean unEnabled(final List<String> areaIdList) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("update bd_area set base_enable=2 where id in (?)");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, areaIdList.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return areaIdList.size();
			}
		});
		return true;
	}

	/**
	 *
	 * @Title: enabled
	 * @Description: 将停用状态改为启用
	 * @author 孟凡岭
	 * @param @param areaIdList
	 * @param @return
	 * @return boolean 返回类型
	 * @throws
	 */
	public boolean enabled(final List<String> areaIdList) {
		StringBuffer sql = new StringBuffer("update bd_area set base_enable=1 where id=?");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, areaIdList.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return areaIdList.size();
			}
		});
		return true;
	}
}
